package com.management.department.teacherinfosystem.Repository;

import com.management.department.teacherinfosystem.Entity.ResearchProjectFunding;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import java.math.BigDecimal;
import java.util.List;

@Repository
public interface ResearchProjectFundingRepository extends JpaRepository<ResearchProjectFunding, Long> {
    
    // 根据项目ID查询经费
    List<ResearchProjectFunding> findByProjectId(Long projectId);
    
    // 根据项目ID删除经费
    void deleteByProjectId(Long projectId);
    
    // 根据经费来源查询
    List<ResearchProjectFunding> findBySource(String source);
    
    // 根据到账状态查询
    List<ResearchProjectFunding> findByStatus(String status);
    
    // 计算项目总经费
    @Query("SELECT COALESCE(SUM(f.amount), 0) FROM ResearchProjectFunding f WHERE f.projectId = :projectId")
    BigDecimal calculateTotalFundingByProjectId(@Param("projectId") Long projectId);
    
    // 计算已到账经费
    @Query("SELECT COALESCE(SUM(f.amount), 0) FROM ResearchProjectFunding f WHERE f.projectId = :projectId AND f.status = '已到账'")
    BigDecimal calculateReceivedFundingByProjectId(@Param("projectId") Long projectId);
}
